# -*- coding: utf-8 -*-
"""
    db.proxy
    ~~~~~~~~

    Proxy to MySQL.

    :copyright: (c) 2015 by Yu Jianjian
    :license: iqiyi.com
"""

import os
import logging
try:
    import Queue
except ImportError:
    import queue as Queue

from collections import defaultdict

from ._rdb import MysqlProxy, MysqlLiteral
from . import DB_CFG


LOG = logging.getLogger(__name__)


class Session(object):

    proxies = defaultdict(Queue.LifoQueue)

    def __init__(self, host, port, user, password, dbname):
        conn_key = '%s@%s:%s/%s' % (user, host, port, dbname)
        self.conn_key = conn_key
        self._proxy = MysqlProxy(host, dbname, user, password, port)

    def _repr(self):
        tid = self._proxy.connection.thread_id()
        return '<Session #%s: %s>' % (tid, self.conn_key)

    def __repr__(self):
        return self._repr()

    def __str__(self):
        return self._repr()

    @classmethod
    def get_session(cls, host, port, user, password, dbname):
        conn_key = '%s@%s:%s/%s' % (user, host, port, dbname)
        conn_q = cls.proxies.get(conn_key, None)
        if conn_q:
            LOG.info('%s connection size: %d', conn_key, conn_q.qsize())
            try:
                while 1:
                    conn = conn_q.get_nowait()
                    if conn.connected:
                        LOG.info('Reused connection in Process:%s: %s', os.getpid(), conn)
                        return conn
                    else:
                        LOG.warning('Dead connection in Process:%s: %s', os.getpid(), conn)
                        conn.close()
            except Queue.Empty:
                # Create a new connection
                LOG.warning('loop over, append connection to Process:%s!', os.getpid())
                return cls(host, port, user, password, dbname)
        else:
            # LOG.info('proxies: %s', cls.proxies)
            LOG.info('New connection in Process:%s!', os.getpid())
            return cls(host, port, user, password, dbname)

    @classmethod
    def release_session(cls, session):
        if not session.connected:
            session.close()
            LOG.warning('%s is lost, remove it !')
        else:
            LOG.debug('release %s to Process:%s!', os.getpid(), session)
            cls.proxies[session.conn_key].put(session)

    @property
    def connected(self):
        return self._proxy.ping()

    def request(self, method, pre_sql):
        _method = getattr(self, method, None)
        if _method and callable(_method):
            return _method(pre_sql)
        else:
            raise Exception('%s with `%r` is not available!' % (method, pre_sql))

    def sql(self, sql):
        result = self._proxy.sql(sql)
        return result

    def get(self, sql):
        result = self._proxy.get(sql)
        return result

    def one(self, sql):
        return self._proxy.one(sql)

    def mod(self, sql):
        result = self._proxy.mod(sql)
        return result

    def add(self, sql):
        result = self._proxy.add(sql)
        return result

    def remove(self, sql):
        result = self._proxy.remove(sql)
        return result

    def join(self, sql):
        result = self._proxy.join(sql)
        return result

    def close(self):
        self._proxy.close()

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        LOG.error('session %s exits', self.conn_key)
        self.close()


def request(method, url, **kwargs):
    session = Session.get_session(DB_CFG['host'], DB_CFG['port'], DB_CFG['user'], DB_CFG['password'], DB_CFG['db'])

    try:
        if method == 'get':
            result = session.request('get', sql_get(url, **kwargs))

        elif method == 'one':
            result = session.request('one', sql_get(url, **kwargs))

        elif method == 'mod':
            result = session.request('mod', sql_mod(url, **kwargs))

        elif method == 'add':
            result = session.request('add', sql_add(url, **kwargs))

        elif method == 'remove':
            result = session.request('remove', sql_remove(url, **kwargs))

        elif method == 'join':
            s = sql_join(url, **kwargs)
            LOG.debug('join sql: %s', s)
            result = session.request('join', s)

        elif method == 'sql':
            result = session.request('sql', url)

        else:
            result = (1, 'not supported')
            LOG.error('Should never being here, just in case!')
    finally:
        Session.release_session(session)

    return result


def where_in(v):
    _result = []
    for _v in v:
        if isinstance(_v, (int, long)):
            _v = '%d' % _v
        else:
            _v = '"%s"' % _v
        _result.append(_v)
    LOG.debug('where_in: %r', _result)
    return '(' + ','.join(_result) + ')'


def sql_get(url, where, **kwargs):
    """ Generate `SELECT` statement.

    :param: url is instance of `URL` at the top.
    :param: where is a dict where_clause.
    """
    pre_sql = u'SELECT {which_clause} FROM {table} {where_clause} {limit_clause};'

    # {which_clause}
    which = kwargs.get('which')

    if isinstance(which, (list, tuple)):
        which_clause = ','.join([k for k in which])
    else:
        which_clause = which

    # {table}
    table = url

    # {where_clause}
    where_grouping = ' ' + kwargs.get('where_grouping') + ' '

    if isinstance(where, dict):
        _where = []
        for k, v in where.items():
            if isinstance(v, (int, long)):
                fmt = '%s=%d'
            elif isinstance(v, (list, tuple)):
                fmt = '%s IN %s'
                v = where_in(v)
            elif v is None:
                fmt = '%s is %s'
                v = 'NULL'
            elif isinstance(v, MysqlLiteral):
                if str(v).lower() == 'null':
                    fmt = '%s IS %s'
                else:
                    fmt = '%s=%s'
            else:
                fmt = '%s="%s"'

            _where.append(fmt % (k, v))

        where_clause = 'WHERE ' + where_grouping.join(_where)
    elif isinstance(where, basestring):
        where_clause = where
    else:
        where_clause = ''

    # {limit_clause}
    limit = kwargs.get('limit')
    if limit is None or not isinstance(limit, (int, long)):
        limit_clause = ''
    else:
        limit_clause = 'LIMIT %s' % limit

    sql = pre_sql.format(which_clause=which_clause,
                         table=table,
                         where_clause=where_clause,
                         limit_clause=limit_clause)
    LOG.debug('sql_get: %s', sql)

    return sql


def sql_mod(url, values, where, **kwargs):
    """ Generate a `UPDATE` statement.
    """

    pre_sql = u'UPDATE {table} SET {values} {where_clause};'

    # {table}
    table = url

    # {values}
    _properties = []
    for k, v in values.items():
        if isinstance(v, (int, long)):
            fmt = '%s=%d'
        elif isinstance(v, MysqlLiteral):
            fmt = '%s=%s'
        else:
            fmt = '%s="%s"'
        _properties.append(fmt % (k, v))

    properties = ','.join(_properties)

    # {where_clause}
    where_grouping = ' ' + kwargs.get('where_grouping') + ' '

    if isinstance(where, dict):
        _where = []
        for k, v in where.items():
            if isinstance(v, (int, long)):
                fmt = '%s=%d'
            elif isinstance(v, (list, tuple)):
                fmt = '%s IN %s'
                v = where_in(v)
            elif isinstance(v, MysqlLiteral):
                if str(v).lower() == 'null':
                    fmt = '%s IS %s'
                else:
                    fmt = '%s=%s'
            else:
                fmt = '%s="%s"'

            _where.append(fmt % (k, v))

        where_clause = 'WHERE ' + where_grouping.join(_where)
    elif isinstance(where, basestring):
        where_clause = where
    else:
        where_clause = ''

    sql = pre_sql.format(table=table, values=properties, where_clause=where_clause)

    return sql


def sql_add(url, values, **kwargs):
    """ Generate a `INSERT` statement.
    """

    pre_sql = u'INSERT INTO {table} ({properties}) VALUES({values});'

    # {table}
    table = url

    # {properties}
    properties, vals = [], []
    for k, v in values.iteritems():
        properties.append(k)

        if isinstance(v, (int, long)):
            fmt = u'%d'
        elif v is None:
            fmt = u'%s'
            v = 'NULL'
        elif isinstance(v, MysqlLiteral):
            fmt = u'%s'
        else:
            fmt = u'"%s"'

        vals.append(fmt % v)

    sql = pre_sql.format(table=table,
                         properties=','.join(properties),
                         values=','.join(vals))
    return sql


def sql_remove(url, where, **kwargs):
    """ Generate a `DELETE` statement.
    """

    pre_sql = u'DELETE FROM {table} {where_clause};'

    # {table}
    table = url

    # {where_clause}
    where_grouping = ' ' + kwargs.get('where_grouping') + ' '

    if isinstance(where, dict):
        _where = []
        for k, v in where.items():
            if isinstance(v, (int, long)):
                fmt = '%s=%d'
            elif isinstance(v, (list, tuple)):
                fmt = '%s IN %s'
                v = where_in(v)
            elif isinstance(v, MysqlLiteral):
                if str(v).lower() == 'null':
                    fmt = '%s IS %s'
                else:
                    fmt = '%s=%s'
            else:
                fmt = '%s="%s"'

            _where.append(fmt % (k, v))

        where_clause = 'WHERE ' + where_grouping.join(_where)
    elif isinstance(where, basestring):
        where_clause = where
    else:
        where_clause = ''

    sql = pre_sql.format(table=table, where_clause=where_clause)

    return sql


def sql_join(urls, where, **filters):
    """ Generate a join `SELECT` statement.

    join(['foo', 'bar'], where={'{0}.id': '{1}.id', '{1}.id': 111})
    => select * from foo, bar where foo.id=bar.id AND foo.id=111;

    join(['foo', 'bar'], where={'{0}.id': 111}, which=('{0}.id',))
    => select foo.id from foo, bar where foo.id=111;
    """
    pre_sql = u'SELECT {which_clause} FROM {tables} {where_clause} {limit};'

    # filters.setdefault('which', '*')
    # filters.setdefault('where_grouping', 'AND')

    # {which_clause}
    which = filters.get('which')

    if isinstance(which, (list, tuple)):
        which_clause = ','.join([k for k in which])
    else:
        which_clause = which

    # {tables}
    tables = ','.join(urls)

    # {where_clause}
    where_grouping = ' ' + filters.get('where_grouping') + ' '

    if isinstance(where, dict):
        _where = []
        for k, v in where.items():
            if isinstance(v, (int, long)):
                fmt = '%s=%d'
            elif isinstance(v, (list, tuple)):
                fmt = '%s IN %s'
                v = where_in(v)
            elif isinstance(v, MysqlLiteral):
                if str(v).lower() == 'null':
                    fmt = '%s IS %s'
                else:
                    fmt = '%s=%s'
            else:
                if '{' in v:
                    fmt = '%s=%s'
                    v = v.format(*urls)
                else:
                    fmt = '%s="%s"'

            _where.append(fmt % (k.format(*urls), v))

        where_clause = 'WHERE ' + where_grouping.join(_where)
    elif isinstance(where, basestring):
        where_clause = where
    else:
        where_clause = ''

    # {limit}
    limit = filters.get('limit')
    if limit is None or not isinstance(limit, (int, long)):
        limit_clause = ''
    else:
        limit_clause = 'LIMIT %s' % limit

    sql = pre_sql.format(which_clause=which_clause,
                         tables=tables,
                         where_clause=where_clause,
                         limit=limit_clause)

    return sql
